package com.xb;

import com.xb.util.dbUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * 使用PreparedStatement进行批量操作
 *
 */
public class PreparedStatementBatchInsert {
    @Test
    public void BatchInsert() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = dbUtil.getConnection();
            String sql = "insert into person(name) values(?)";
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < 10000; i++) {
                ps.setObject(1, "name_" + i);
                ps.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println("插入10000条使用的时间为 " + (end - start)); //23987
        } catch (Exception e) {
            dbUtil.closeConAndPs(connection, ps);
        }
    }

    /**
     * 这个优化就类似于File的缓存区btye[]
     */
    @Test
    public void BatchInsert02() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = dbUtil.getConnection();
            String sql = "insert into person(name) values(?)";
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < 10000; i++) {
                ps.setObject(1, "name_" + i);
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("插入10000条使用的时间为 " + (end - start)); // 14364
        } catch (Exception e) {
            dbUtil.closeConAndPs(connection, ps);
        }
    }

    /**
     * 这个优化是处理在因为DML每次操作都会进行数据库的事务的提交，这个时候就会存在消耗时间的问题
     */
    @Test
    public void BatchInsert03() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = dbUtil.getConnection();
            connection.setAutoCommit(false);
            String sql = "insert into person(name) values(?)";
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < 10000; i++) {
                ps.setObject(1, "name_" + i);
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            connection.commit();
            long end = System.currentTimeMillis();
            System.out.println("插入10000条使用的时间为 " + (end - start)); // 2408
        } catch (Exception e) {
            dbUtil.closeConAndPs(connection, ps);
        }
    }
}
